using Dapper;
using Microsoft.Extensions.Configuration;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using mapshiftApplication.Dto;

namespace mapshiftApplication.Repositories
{
    public class TsmcConfigRepository : ITsmcConfigRepository
    {
        private readonly string _connectionString;

        public TsmcConfigRepository(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection")!;
        }

        public async Task<IEnumerable<TsmcConfigDto>> GetAllAsync()
        {
            await using var connection = new OracleConnection(_connectionString);
            var sql = "SELECT MTD_ID as Id, MTD_CUST as MtdCust, MTD_DEVICE as MtdDevice, MTD_REMARK as MtdRemark FROM MMS_TSMC_DEVICE";
            return await connection.QueryAsync<TsmcConfigDto>(sql);
        }

        public async Task<TsmcConfigDto> GetByIdAsync(string id)
        {
            await using var connection = new OracleConnection(_connectionString);
            var sql = "SELECT MTD_ID as Id, MTD_CUST as MtdCust, MTD_DEVICE as MtdDevice, MTD_REMARK as MtdRemark FROM MMS_TSMC_DEVICE WHERE MTD_ID = :Id";
            return (await connection.QueryFirstOrDefaultAsync<TsmcConfigDto>(sql, new { Id = id }))!;
        }

        public async Task<string> AddAsync(TsmcConfigDto config)
        {
            await using var connection = new OracleConnection(_connectionString);
            config.Id = Guid.NewGuid().ToString();
            var sql = "INSERT INTO MMS_TSMC_DEVICE (MTD_ID, MTD_CUST, MTD_DEVICE, MTD_REMARK) VALUES (:Id, :MtdCust, :MtdDevice, :MtdRemark)";
            await connection.ExecuteAsync(sql, config);
            return config.Id;
        }

        public async Task<int> UpdateAsync(TsmcConfigDto config)
        {
            await using var connection = new OracleConnection(_connectionString);
            var sql = "UPDATE MMS_TSMC_DEVICE SET MTD_CUST = :MtdCust, MTD_DEVICE = :MtdDevice, MTD_REMARK = :MtdRemark WHERE MTD_ID = :Id";
            return await connection.ExecuteAsync(sql, config);
        }

        public async Task<int> DeleteAsync(string id)
        {
            await using var connection = new OracleConnection(_connectionString);
            var sql = "DELETE FROM MMS_TSMC_DEVICE WHERE MTD_ID = :Id";
            return await connection.ExecuteAsync(sql, new { Id = id });
        }
    }
}
